﻿using FCDAL;
using FCModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Collections;
using System.Reflection;
using System.Text.RegularExpressions;

namespace FCBLL
{
    public class CommonBLL
    {

        public static bas_TestType getValueIndexAndHeadIndex(string fileType)
        {
            return new BaseRepositoryDAL<bas_TestType>().LoadEntities(p => p.testType.Equals(fileType)).FirstOrDefault();// tpDAL.getValueIndexAndHeadIndex(fileType);
        }


        public static BOM_Head getBOM_HeadByBOMType(string BOMType)
        {
            return new BaseRepositoryDAL<BOM_Head>().LoadEntities(p => p.BOMType.Equals(BOMType)).FirstOrDefault();// tpDAL.getValueIndexAndHeadIndex(fileType);
        }

        /// <summary>
        /// ExcelToDT
        /// </summary>
        /// <param name="Path"></param>
        /// <returns></returns>
        public static DataSet ExcelToDS(string Path)
        {
            //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Path + ";Extended Properties='Excel 12.0; HDR=yes; IMEX=0'";
            OleDbConnection conn = new OleDbConnection(strConn);
            conn.Open();
            try
            {
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = null;
                strExcel = "select * from [sheet1$]";
                myCommand = new OleDbDataAdapter(strExcel, strConn);
                ds = new DataSet();
                myCommand.Fill(ds, "table1");
                //strExcel = "select * from [sheet2$]";
                //myCommand = new OleDbDataAdapter(strExcel, strConn);
                //myCommand.Fill(ds, "table2");
                conn.Close();
                conn.Dispose();
                myCommand.Dispose();
                return ds;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static DataTable CSVTODS(string csvFile, int startIdx, Encoding ec, DataTable dtSchema = null, string columnNames = "")
        {
            Regex spliter = new Regex(",(?=[^\"]*((\"[^\"]*){2})*$)", RegexOptions.Compiled | RegexOptions.ExplicitCapture);

            DataTable dt = new DataTable();
            FileInfo fi = new FileInfo(csvFile);
            string[] fiLS = ConvertFileToStr(fi, ec);
            for (int i = startIdx; i < fiLS.Length; i++)
            {
                string lineStr = fiLS[i];
                if (lineStr.Replace(",", "").Trim() == "")
                    continue;
                //string[] arr = spliter.Split(lineStr);
                //string[] ls_lineStr = lineStr.Split(',');
                string[] ls_lineStr = spliter.Split(lineStr);
                if (i == startIdx)
                {
                    if (columnNames == "")
                    {
                        for (int i_field = 0; i_field < ls_lineStr.Length; i_field++)
                        {
                            dt.Columns.Add(ls_lineStr[i_field].ToString().Trim());
                        }
                    }
                    else
                    {
                        string[] columnName = columnNames.Split(new char[] { ';' }, StringSplitOptions.RemoveEmptyEntries);
                        for (int name = 0; name < columnName.Length; name++)
                        {
                            dt.Columns.Add(columnName[name].ToString().Trim().Trim(new char[] { '\"' }));
                        }
                        if (dt.Columns.Count < ls_lineStr.Length)
                        {
                            throw new Exception("str columns over field count!\r" + lineStr);
                        }
                        DataRow myRow = dt.NewRow();
                        for (int i_field = 0; i_field < ls_lineStr.Length; i_field++)
                        {
                            myRow[i_field] = ls_lineStr[i_field].Trim().Trim(new char[] { '\"' });
                        }
                        dt.Rows.Add(myRow);
                    }
                }
                else
                {
                    if (dt.Columns.Count < ls_lineStr.Length)
                    {
                        throw new Exception("str columns over field count!\r" + lineStr);
                    }
                    DataRow myRow = dt.NewRow();
                    for (int i_field = 0; i_field < ls_lineStr.Length; i_field++)
                    {
                        myRow[i_field] = ls_lineStr[i_field].Trim(new char[] { '\"' });
                        bool dontTrim = false;
                        if (dtSchema != null)
                        {
                            DataRow[] drs_schemas = dtSchema.Select("columnname='" + dt.Columns[i_field].ColumnName + "'");
                            if (drs_schemas.Length == 1)
                            {
                                dontTrim = Convert.ToBoolean(drs_schemas[0]["dontTrim"]);
                            }
                        }
                        if (!dontTrim) myRow[i_field] = myRow[i_field].ToString().Trim();
                    }
                    dt.Rows.Add(myRow);
                }
            }
            return dt;
        }

        /// <summary>
        /// ExcelToDS
        /// </summary>
        /// <param name="Path"></param>
        /// <returns></returns>
        public static DataSet ExcelToDSBysheets(string Path)
        {
            //此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)  
            string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + Path + ";Extended Properties='Excel 12.0; HDR=yes; IMEX=0'";
            OleDbConnection conn = new OleDbConnection(strConn);
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                strConn = "Provider=Microsoft.Jet.Oledb.4.0;Data Source=" + Path + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";   
                conn = new OleDbConnection(strConn);
                conn.Open();
            }
            try
            {
                string strExcel = "";
                OleDbDataAdapter myCommand = null;
                DataSet ds = new DataSet();
                DataSet resultDS = new DataSet();
                DataTable sheetDT = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                //sheetDT.AsEnumerable().OrderBy(p => p.Field<string>("as"));
                if (sheetDT == null)
                {
                    return ds;
                }
                foreach (DataRow dr in sheetDT.Rows)
                {
                    string sheetName = dr["TABLE_NAME"].ToString();
                    if (sheetName.Contains("flowchart")) { continue; }
                    if (sheetName.Contains("$"))
                    {
                        if (sheetName.Split('$')[1].Length == 0 || sheetName.Split('$')[1] == "'")
                        {
                            strExcel = string.Format(@"select * from [{0}]", sheetName);
                            myCommand = new OleDbDataAdapter(strExcel, strConn);
                            myCommand.Fill(ds, sheetName.Trim('$'));
                        }
                    }
                }
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    DataTable dt = ds.Tables[i];
                    if (dt.TableName.StartsWith("'M"))
                    {
                        DataTable Mdt = dt.Copy();
                        resultDS.Tables.Add(Mdt);
                        ds.Tables.Remove(dt);
                        break;
                    }
                }
                for (int i = 0; i < ds.Tables.Count; i++)
                {
                    DataTable fdt = ds.Tables[i].Copy();
                    resultDS.Tables.Add(fdt);
                }
                conn.Close();
                conn.Dispose();
                myCommand.Dispose();
                return resultDS;
            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static DataSet AccessToDS(string tableName, string grNO, string mdbPath, ref bool success)
        {
            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            try
            {
                DataRow dr;
                //1、建立连接 C#操作Access之读取mdb  
                string strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
                + mdbPath + ";";//Jet OLEDB:Database  Password=haoren
                OleDbConnection odcConnection = new OleDbConnection(strConn);
                //2、打开连接 C#操作Access之读取mdb  
                odcConnection.Open();
                //建立SQL查询   
                OleDbCommand odCommand = odcConnection.CreateCommand();
                //3、输入查询语句 C#操作Access之读取mdb  
                odCommand.CommandText = string.Format(@"SELECT * from {0} where gr_no = '{1}' order by lot_no", tableName, grNO);
                //建立读取   
                OleDbDataReader odrReader = odCommand.ExecuteReader();
                //查询并显示数据   
                int size = odrReader.FieldCount;
                for (int i = 0; i < size; i++)
                {
                    DataColumn dc;
                    dc = new DataColumn(odrReader.GetName(i));
                    dt.Columns.Add(dc);
                }
                while (odrReader.Read())
                {
                    dr = dt.NewRow();
                    for (int i = 0; i < size; i++)
                    {
                        dr[odrReader.GetName(i)] =
                        odrReader[odrReader.GetName(i)].ToString();
                    }
                    dt.Rows.Add(dr);
                }
                //关闭连接 C#操作Access之读取mdb  
                odrReader.Close();
                odcConnection.Close();
                success = true;
                ds.Tables.Add(dt);
                return ds;
            }
            catch
            {
                success = false;
                return ds;
            }
        }

        public static DataTable ParameterDaoToDataTable(IList<ParameterDao> listSSD, string partNo)
        {
            //VPC 4D FPC Data																																		
            //GR#:																																					
            //S/N	Parameter	Nominal	Max	Min	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29				
            DataTable dt = new DataTable();
            dt.Columns.Add("S/N", typeof(System.String));
            dt.Columns.Add("Parameter", typeof(System.String));
            dt.Columns.Add("Nominal", typeof(System.String));
            dt.Columns.Add("Max", typeof(System.String));
            dt.Columns.Add("Min", typeof(System.String));
            foreach (ParameterDao ssd in listSSD)
            {
                dt.Columns.Add(ssd.SeqNo, typeof(System.String));
            }
            var vParameterName = listSSD[0].listSSD.OrderBy(x => x.ParameterSortName).Select(p => p.ParameterName).Distinct();
            int iRow = 0;
            IList<ParameterRange> listPR = new BaseRepositoryDAL<ParameterRange>().LoadEntities(p => p.partNo.Equals(partNo, StringComparison.OrdinalIgnoreCase)).ToList<ParameterRange>();
            //iqcen.ParameterRange
            foreach (string ParameterName in vParameterName)
            {
                DataRow dr = dt.NewRow();
                dr["S/N"] = (++iRow).ToString();
                dr["Parameter"] = ParameterName;
                //通过基础数据获取标准值上下限值
                //dr["Nominal"] = listSSD[0].listSSD.Where(p=>p.ParameterName.Equals(ParameterName, StringComparison.OrdinalIgnoreCase)).ToList<Parameter>()[0].StandardValue;
                //dr["Max"] = listSSD.Select(p => p.listSSD.Where(x=>x.ParameterName.Equals(ParameterName, StringComparison.OrdinalIgnoreCase)).Select(pp => pp.ParameterValue).Max()).Max();
                //dr["Min"] = listSSD.Select(p => p.listSSD.Where(x => x.ParameterName.Equals(ParameterName, StringComparison.OrdinalIgnoreCase)).Select(pp => pp.ParameterValue).Min()).Min();
                var vStandard = listPR.Where(p => p.Parameter.Equals(ParameterName, StringComparison.OrdinalIgnoreCase)).ToList<ParameterRange>();
                if (vStandard != null && vStandard.Count() > 0)
                {
                    dr["Nominal"] = vStandard[0].Nominal;
                    dr["Max"] = vStandard[0].Max;
                    dr["Min"] = vStandard[0].Min;
                }
                foreach (ParameterDao ssd in listSSD)
                {
                    var vqry = ssd.listSSD.Where(p => p.ParameterName.Equals(ParameterName,
                        StringComparison.OrdinalIgnoreCase)).ToArray<Parameter>().FirstOrDefault();
                    if (vqry != null)
                    {
                        dr[ssd.SeqNo] = vqry.ParameterValue;
                    }
                }
                dt.Rows.Add(dr);
            }
            return dt;
        }

        public static DataTable BOMParameterDaoToDataTable(IList<BOMParameterDao> listSSD, string BomType)
        {
            //VPC 4D FPC Data																																		
            //GR#:																																					
            //S/N	Parameter	Nominal	Max	Min	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29				
            DataTable dt = new DataTable();
            //dt.Columns.Add("S/N", typeof(System.String));
            //dt.Columns.Add("Parameter", typeof(System.String));
            //dt.Columns.Add("Nominal", typeof(System.String));
            //dt.Columns.Add("Max", typeof(System.String));
            //dt.Columns.Add("Min", typeof(System.String));
            if (listSSD.Count > 0)
            {
                for (int i = 0; i < listSSD[0].listSSD.Count; i++)
                {
                    dt.Columns.Add(listSSD[0].listSSD[i].ParameterName, typeof(System.String));
                }
                foreach (BOMParameterDao ssd in listSSD)
                {
                    DataRow dr = dt.NewRow();
                    for (int i = 0; i < ssd.listSSD.Count; i++)
                    {
                        dr[ssd.listSSD[i].ParameterName] = ssd.listSSD[i].ParameterValue;
                    }
                    dt.Rows.Add(dr);
                }
            }
            //var vParameterName = listSSD[0].listSSD.OrderBy(x => x.ParameterSortName).Select(p => p.ParameterName).Distinct();
            //int iRow = 0;
            //IList<ParameterRange> listPR = new BaseRepositoryDAL<ParameterRange>().LoadEntities(p => p.partNo.Equals(partNo, StringComparison.OrdinalIgnoreCase)).ToList<ParameterRange>();
            ////iqcen.ParameterRange
            //foreach (string ParameterName in vParameterName)
            //{
            //    DataRow dr = dt.NewRow();
            //    dr["S/N"] = (++iRow).ToString();
            //    dr["Parameter"] = ParameterName;
            //    //通过基础数据获取标准值上下限值
            //    //dr["Nominal"] = listSSD[0].listSSD.Where(p=>p.ParameterName.Equals(ParameterName, StringComparison.OrdinalIgnoreCase)).ToList<Parameter>()[0].StandardValue;
            //    //dr["Max"] = listSSD.Select(p => p.listSSD.Where(x=>x.ParameterName.Equals(ParameterName, StringComparison.OrdinalIgnoreCase)).Select(pp => pp.ParameterValue).Max()).Max();
            //    //dr["Min"] = listSSD.Select(p => p.listSSD.Where(x => x.ParameterName.Equals(ParameterName, StringComparison.OrdinalIgnoreCase)).Select(pp => pp.ParameterValue).Min()).Min();
            //    var vStandard = listPR.Where(p => p.Parameter.Equals(ParameterName, StringComparison.OrdinalIgnoreCase)).ToList<ParameterRange>();
            //    if (vStandard != null && vStandard.Count() > 0)
            //    {
            //        dr["Nominal"] = vStandard[0].Nominal;
            //        dr["Max"] = vStandard[0].Max;
            //        dr["Min"] = vStandard[0].Min;
            //    }
            //    foreach (ParameterDao ssd in listSSD)
            //    {
            //        var vqry = ssd.listSSD.Where(p => p.ParameterName.Equals(ParameterName,
            //            StringComparison.OrdinalIgnoreCase)).ToArray<Parameter>().FirstOrDefault();
            //        if (vqry != null)
            //        {
            //            dr[ssd.SeqNo] = vqry.ParameterValue;
            //        }
            //    }
            //    dt.Rows.Add(dr);
            //}
            return dt;
        }
        /// <summary>  
        /// 泛型集合转换DataSet  
        /// </summary>  
        /// <typeparam name="T"></typeparam>  
        /// <param name="list">泛型集合</param>  
        /// <returns></returns>  
        /// 2008-08-01 22:43 HPDV2806  
        public static DataSet ToDataSet<T>(IList<T> list)
        {
            return ToDataSet<T>(list, null);
        }

        /// <summary>  
        /// 泛型集合转换DataSet  
        /// </summary>  
        /// <typeparam name="T"></typeparam> 
        /// <param name="p_List">泛型集合</param>
        /// <param name="p_PropertyName">待转换属性名数组</param>  
        /// <returns></returns>  
        /// 2008-08-01 22:44 HPDV2806  
        public static DataSet ToDataSet<T>(IList<T> p_List, params string[] p_PropertyName)
        {
            List<string> propertyNameList = new List<string>();
            if (p_PropertyName != null)
                propertyNameList.AddRange(p_PropertyName);
            DataSet result = new DataSet();
            DataTable _DataTable = new DataTable();
            if (p_List.Count > 0)
            {
                PropertyInfo[] propertys = p_List[0].GetType().GetProperties();
                foreach (PropertyInfo pi in propertys)
                {
                    if (propertyNameList.Count == 0)
                    {
                        // 没有指定属性的情况下全部属性都要转换  
                        _DataTable.Columns.Add(pi.Name, pi.PropertyType);
                    }
                    else
                    {
                        if (propertyNameList.Contains(pi.Name))
                            _DataTable.Columns.Add(pi.Name, pi.PropertyType);
                    }
                }

                for (int i = 0; i < p_List.Count; i++)
                {
                    ArrayList tempList = new ArrayList();
                    foreach (PropertyInfo pi in propertys)
                    {
                        if (propertyNameList.Count == 0)
                        {
                            object obj = pi.GetValue(p_List[i], null);
                            tempList.Add(obj);
                        }
                        else
                        {
                            if (propertyNameList.Contains(pi.Name))
                            {
                                object obj = pi.GetValue(p_List[i], null);
                                tempList.Add(obj);
                            }
                        }
                    }
                    object[] array = tempList.ToArray();
                    _DataTable.LoadDataRow(array, true);
                }
            }

            result.Tables.Add(_DataTable);
            return result;
        }

        /// <summary>
        /// DataSetToStringArray
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static string[] DataSetToStringArray(DataSet ds)
        {
            DataTable dt = ds.Tables[0];
            IList<string> stringList = new List<string>();
            //获取列名
            string colName = "";
            for (int k = 0; k < dt.Columns.Count; k++)
            {
                colName += dt.Columns[k].ColumnName.ToString() + ",";
            }
            stringList.Add(colName);
            //获取值
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string ArrayLine = "";
                if (string.IsNullOrWhiteSpace(dt.Rows[i][0].ToString()))
                {
                    break;
                }
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ArrayLine += dt.Rows[i][j].ToString() + ",";
                }
                stringList.Add(ArrayLine);
            }
            return stringList.ToArray<string>();
        }
        /// <summary>
        /// DataSetToStringArray
        /// </summary>
        /// <param name="ds"></param>
        /// <returns></returns>
        public static string[] DataSetToStringArrayNull(DataSet ds)
        {
            DataTable dt = ds.Tables[0];
            IList<string> stringList = new List<string>();
            //获取列名
            string colName = "";
            for (int k = 0; k < dt.Columns.Count; k++)
            {
                colName += dt.Columns[k].ColumnName.ToString() + ";";
            }
            stringList.Add(colName);
            //获取值
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                string ArrayLine = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    ArrayLine += dt.Rows[i][j].ToString() + ";";
                }
                stringList.Add(ArrayLine);
            }
            return stringList.ToArray<string>();
        }

        public static void WriteLog(string path, string LogText)
        {
            string strLogFilePath = path + "FileConverter.log";
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
            //StreamWriter logWriter;
            //logWriter = File.AppendText(strLogFilePath);
            //logWriter.Write(DateTime.Now.ToString() + ": ");
            //logWriter.WriteLine(LogText);
            //logWriter.Close();

            using (StreamWriter sw = new StreamWriter(strLogFilePath, true))
            {
                sw.WriteLine(DateTime.Now.ToString() + ": " + LogText);
            }
        }

        public static string[] ConvertFileToStr(FileInfo fi, Encoding ec)
        {
            string[] strFile;
            string srcStr;

            try
            {
                using (StreamReader sr = new StreamReader(fi.OpenRead(), ec, true))
                {
                    if (sr.Peek() > -1)
                    {
                        srcStr = sr.ReadToEnd().Trim();
                        strFile = srcStr.Split('\n');
                    }
                    else
                    {
                        strFile = null;
                        return strFile;
                    }
                    return strFile;
                }
            }
            catch
            {
                throw;
            }
        }

        //public static void ExportToTxtFile(DataTable dgv, string filename, bool headTextFlag)
        //{
        //    string strLine = " ";
        //    if ((headTextFlag == false) && (dgv.Rows.Count < 1)) //不需要表头时，必需有数据
        //    {
        //        //MessageBox.Show("No Data");
        //    }
        //    else
        //    {
        //        StreamWriter sr = new StreamWriter(filename, false, Encoding.GetEncoding("GB2312"));//, Encoding.GetEncoding("GB2312")
        //        //StreamWriter sr = File.CreateText(filename);
        //        for (int i_col = 0; i_col < dgv.Columns.Count; i_col++)  //表头列名
        //        {
        //            string dgvHeadText = "";
        //            dgvHeadText = dgv.Columns[i_col].ColumnName.ToString();
        //            if (i_col < dgv.Columns.Count - 1)
        //                strLine += dgvHeadText + ",";
        //            else
        //                strLine += dgvHeadText;
        //        }
        //        sr.WriteLine(strLine);
        //        strLine = "";
        //        if (headTextFlag)
        //        {
        //            for (int i_col = 0; i_col < dgv.Columns.Count; i_col++)   //表头列标题
        //            {
        //                string dgvHeadText = "";
        //                dgvHeadText = dgv.Columns[i_col].HeaderText.ToString();

        //                if (i_col < dgv.Columns.Count - 1)
        //                    strLine += dgvHeadText + ",";
        //                else
        //                    strLine += dgvHeadText;
        //            }
        //        }
        //        sr.WriteLine(strLine);
        //        strLine = "";
        //        for (int i = 0; i < dgv.Rows.Count; i++)
        //        {
        //            for (int i_col = 0; i_col < dgv.Rows[i].Cells.Count; i_col++)
        //            {
        //                if (i_col < dgv.Rows[i].Cells.Count - 1)
        //                {
        //                    if (dgv.Columns[i_col].Visible == false)
        //                        continue;
        //                    if (dgv.Rows[i].Cells[i_col].Value != null)
        //                        strLine += dgv.Rows[i].Cells[i_col].Value.ToString() + ",";
        //                    else
        //                        strLine += ",";
        //                }
        //                else
        //                {
        //                    if (dgv.Rows[i].Cells[i_col].Value != null)
        //                        strLine += dgv.Rows[i].Cells[i_col].Value.ToString();
        //                    else
        //                        strLine += "";
        //                }
        //            }
        //            sr.WriteLine(strLine);
        //            strLine = "";
        //        }
        //        sr.Close();
        //    }
        //}

        //public static bool ConvertExcelToCSV(string sourceExcelPathAndName, string targetCSVPathAndName, string excelSheetName, string columnDelimeter, int headerRowsToSkip)
        //{
        //    Microsoft.Office.Interop.Excel.Application oXL= new Microsoft.Office.Interop.Excel.Application();
        //    Microsoft.Office.Interop.Excel.Workbooks workbooks = oXL.Workbooks;
        //    Workbook mWorkBook=workbooks.Open(sourceExcelPathAndName, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);;
        //    Sheets mWorkSheets = mWorkBook.Worksheets;
        //    Worksheet mWSheet1=(Worksheet)mWorkSheets.get_Item(excelSheetName);
        //    try
        //    {
        //        oXL.Visible = false;
        //        oXL.DisplayAlerts = false;
        //        Microsoft.Office.Interop.Excel.Range range = mWSheet1.UsedRange;
        //        Microsoft.Office.Interop.Excel.Range rngCurrentRow;
        //        for (int i = 0; i < headerRowsToSkip; i++)
        //        {
        //            rngCurrentRow = range.get_Range("A1", Type.Missing).EntireRow;
        //            rngCurrentRow.Delete(XlDeleteShiftDirection.xlShiftUp);
        //        }
        //        range.Replace("\n", " ", Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        //        range.Replace(",", columnDelimeter, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
        //        mWorkBook.SaveAs(targetCSVPathAndName, XlFileFormat.xlCSVMSDOS,
        //        Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlExclusive,
        //        Type.Missing, Type.Missing, Type.Missing,
        //        Type.Missing, false);
        //        return true;
        //    }
        //    catch (Exception ex)
        //    {
        //         ex.ToString();
        //        return false;
        //    }
        //    finally
        //    {
        //        if (mWSheet1 != null) mWSheet1 = null;
        //        if (mWorkBook != null) mWorkBook.Close(Type.Missing, Type.Missing, Type.Missing);
        //        if (mWorkBook != null) mWorkBook = null;
        //        if (oXL != null) oXL.Quit();
        //        System.Runtime.InteropServices.Marshal.ReleaseComObject(oXL);
        //        if (oXL != null) oXL = null;
        //        GC.WaitForPendingFinalizers();
        //        GC.Collect();
        //        GC.WaitForPendingFinalizers();
        //        GC.Collect();
        //    }
        //}   

        public static DataTable ReadQPMFile(string csvFile)
        {
            DataTable dt = new DataTable();
            string[] strs;
            FileInfo fi = new FileInfo(csvFile);
            try
            {
                using (StreamReader sr = new StreamReader(fi.OpenRead(), Encoding.GetEncoding("GB2312"), true))
                {
                    string str = sr.ReadToEnd();
                    strs = str.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
                }
                string[] strs_col = strs[5].Split(',');   //第六行为列名            
                for (int i = 0; i < strs_col.Length; i++)
                {
                    dt.Columns.Add(strs_col[i].ToString().Trim('"'));
                }
                for (int i = 6; i < strs.Length; i++)
                {
                    string[] val = strs[i].Replace('"', '\r').Split(',');
                    dt.Rows.Add(val);
                }
                dt.Rows[dt.Rows.Count - 1].Delete();
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static DataTable ReadQDMFile(string csvFile)
        {
            DataTable dt = new DataTable();
            string[] strs;
            FileInfo fi = new FileInfo(csvFile);
            try
            {
                using (StreamReader sr = new StreamReader(fi.OpenRead(), Encoding.GetEncoding("GB2312"), true))
                {
                    string str = sr.ReadToEnd();
                    strs = str.Split(new char[] { '\r', '\n' }, StringSplitOptions.RemoveEmptyEntries);
                }
                int x = strs[0].Length;
                string[] strs_col = strs[0].Split(',');                //取第一行作为列名。只需要26列之后的列和数据，否则点击插入按钮时会出错
                for (int i = 0; i < strs_col.Length; i++)
                {
                    dt.Columns.Add(strs_col[i].ToString().Trim('"'));
                }
                for (int i = 1; i < strs.Length; i++)             //放置数据到 dataGridView2
                {
                    string[] val = strs[i].Split(',');
                    dt.Rows.Add(val);
                }
            }
            catch
            {
                throw;
            }
            return dt;
        }

        
    }
}
